import xlwings
import sys
import os
import time
import datetime

# 初始化程序
sys.setrecursionlimit(10000)  # 递归上线
app = xlwings.App(visible=False, add_book=False)  # 创建应用


# 函数库
# 创建Book
def add_book():
    book = app.books.add()
    return book


# 读取Book
def read_book(book_address):
    book = app.books.open(f"{book_address}")
    return book


# 创建Sheet
def add_sheet(work_book, sheet_name, after_name):
    sheet = work_book.sheets.add(f"{sheet_name}", after=after_name)
    return sheet


# 读取Sheet
def read_sheet(work_book, sheet_address):
    sheet = work_book.sheets[sheet_address]
    return sheet


# 写入Sheet
def write_sheet(sheet, sheet_data):
    sheet.range("a1").value = sheet_data


# 获取总行
def get_row_max(sheet):
    info = sheet.used_range  # 找最后行
    row_max = info.last_cell.row
    return row_max


# 初始化原始数据
book_name = 'test.xlsx'  # 原始数据表文件名
original_address = book_name  # 原始数据地址
original_book = read_book(original_address)  # 实例原始数据簿
original_sheet = original_book.sheets[0]  # 实例原始数据表
sheet_row_max = get_row_max(original_sheet)  # 获取原始数据表最大行
print('数据表总行数', sheet_row_max)  # 确认数据表总行数
original_data = original_sheet.range(f"a2:r{sheet_row_max + 1}").value  # 获取原始数据表数据（尾部附加1行空值）
print('数据总行数', len(original_data))  # 确认数据总行数
print('空行：', original_data[sheet_row_max - 1])  # 确认空值是否存在
# 格式化日期与时间
for i in range(sheet_row_max - 1):
    # 格式化日期
    date = original_data[i][10]
    date_str = date.replace('\t', '')
    original_data[i][10] = date_str
    # 格式化时间
    time = original_data[i][11]
    seconds = round(time * 24 * 60 * 60)
    m, s = divmod(seconds, 60)
    h, m = divmod(m, 60)
    time_str = f'{h}:{m}:{s}'
    original_data[i][11] = time_str


# 汇总在岗时间
def sum_times():
    # 初始化
    row = 0  # 实例行数
    times = 0  # 实例在岗总时长
    sum_data = [['姓名', '时长(小时)']]  # 实例时间汇总表数据

    while True:
        # 检验数据表是否缺失
        if os.path.isfile(f'{original_address}') is False:
            print(f'时间数据表：{original_address} 缺失')
            time.sleep(5)
            original_book.save()
            original_book.close()
            exit()

        name = original_data[row][0]  # 获取员工姓名
        direction = original_data[row][14]  # 确认进出门方向

        # 结束判定
        if name is None:
            print('在岗时间汇总完成')
            sum_book = add_book()  # 实例在岗时间汇总数据簿
            sum_sheet = sum_book.sheets[0]  # 实例在岗时间汇总数据表
            sum_sheet.name = '在岗时间汇总表'  # 数据表重命名
            write_sheet(sum_sheet, sum_data)  # 写入数据
            sum_book.save('在岗时间汇总表.xlsx')
            sum_book.close()
            original_book.save()
            return

        # 确认个人信息,记录总时间
        if name != original_data[row + 1][0]:
            hours = round(times / 60, 2)  # 分钟换算小时，保留两位小数
            original_sheet.range(f"s{row + 2}").value = [name, hours]  # 写入数据
            sum_data.append([name, hours])
            row += 1
            times = 0

        else:
            # 个人时长叠加
            if direction == '出':
                times += original_data[row][17]  # 在岗时间累加
            row += 1


# 检查考勤状态
def check_work_attendance():
    row = 0  # 实例行数
    date_mark = None  # 实例日期
    date_mark = datetime.datetime.strptime(original_data[row][10], '%Y-%m-%d')
    while True:
        # 检验数据表是否缺失
        if os.path.isfile(f'{original_address}') is False:
            print(f'时间数据表：{original_address} 缺失')
            time.sleep(5)
            original_book.save()
            original_book.close()
            exit()

        name = original_data[row][0]  # 获取员工姓名
        date_time = datetime.datetime.strptime(original_data[row][10], '%Y-%m-%d')  # 获取员工打卡日期
        date_time_1 =
        time_time = datetime.datetime.strptime(original_data[row + 1][10] + ' ' + original_data[row + 1][11],
                                                '%Y-%m-%d %H:%M:%S')  # 获取员工打卡时间
        direction = original_data[row][14]  # 获取员工打卡方向

        # 结束判定
        if name is None:
            print('员工打卡处理完成')
            original_book.save()
            return

        else:
            if date_time1.day != date_time2.day:
                delta = date_time1

        original_book.save()
        original_book.close()
        return


check_work_attendance()
# 结束判定
# if name is None:
#     print('在岗时间汇总完成')
#     sum_book = add_book()  # 实例在岗时间汇总数据簿
#     sum_sheet = sum_book.sheets[0]  # 实例在岗时间汇总数据表
#     sum_sheet.name = '在岗时间汇总表'  # 数据表重命名
#     write_sheet(sum_sheet, sum_data)  # 写入数据
#     sum_book.save('在岗时间汇总表.xlsx')
#     sum_book.close()
#     original_book.save()
#     return


# print(original_data[0][10])
# print(time.time())
# Time = datetime.datetime.strptime('2022-07-01'' 08:04:00', '%Y-%m-%d %H:%M:%S')
# Time2 = datetime.datetime.strptime('2022-07-01'' 08:08:55', '%Y-%m-%d %H:%M:%S')
# delta = Time2 - Time
# delta_min = datetime.datetime.strptime('0:04:00', '%H:%M:%S')
# print(delta_min)
#
# print(type(Time))
# print(Time + datetime.timedelta(minutes=5))
# st = time.localtime()
# print(st)
